<?php
class DealOrderDABase extends AbstractDbObject {
    /**
     * Table name
     *
     * @var string
     */
    protected $tableName = 'deal_order';

    /**
     * Table alias
     *
     * @var string
     */
    protected $tableAlias = 'deal_order';

    /**
     * Create a deal
     *
     * @access public
     *
     * @param DealOrderEntity $dealOrderEntity: object
     *
     * @return primary key has just been inserted
     */
    public function create($dealOrderEntity) {
        try {
            // Get db object
            $dbWriter = $this->getDbWriter();

            // Fetch array
            $data = array(
                    'user_id' => $dealOrderEntity->user_id,
                    'deal_id' => $dealOrderEntity->deal_id,
                    'order_date' => $dealOrderEntity->order_date,
                    'bar_code' => $dealOrderEntity->bar_code,
                    'quantity' => $dealOrderEntity->quantity,
                    'total_price' => $dealOrderEntity->total_price,
                    'service_fee' => $dealOrderEntity->service_fee,
                    'merchant_money' => $dealOrderEntity->merchant_money,
                    'payment_status' => $dealOrderEntity->payment_status,
		            'order_status' => $dealOrderEntity->order_status,
		            'type' => $dealOrderEntity->type,
                    'payment_method' => $dealOrderEntity->payment_method,
                    'token' => $dealOrderEntity->token,
            );

            // Insert into database
            $dbWriter->insert($this->tableName, $data);

            // Return primary key has just created
            return $dbWriter->lastInsertId($this->tableName, 'id');
        } catch(Exception $ex) {

            throw $ex;
        }
    }

    /**
     * Update a deal
     *
     * @access public
     *
     * @param DealEntity $dealOrderEntity: object
     *
     * @return bool true if update success otherwise return false
     */
    public function update($dealOrderEntity) {
        try {
            // Get db object
            $dbWriter = $this->getDbWriter();

            // Fetch array
            $data = array(
                    'user_id' => $dealOrderEntity->user_id,
                    'deal_id' => $dealOrderEntity->deal_id,
                    'order_date' => $dealOrderEntity->order_date,
                    'bar_code' => $dealOrderEntity->bar_code,
                    'quantity' => $dealOrderEntity->quantity,
                    'total_price' => $dealOrderEntity->total_price,
                    'service_fee' => $dealOrderEntity->service_fee,
                    'merchant_money' => $dealOrderEntity->merchant_money,
                    'payment_status' => $dealOrderEntity->payment_status,
		            'order_status' => $dealOrderEntity->order_status,
		            'type' => $dealOrderEntity->type,
                    'payment_method' => $dealOrderEntity->payment_method,
                    'token' => $dealOrderEntity->token,
                    'gateway_transaction_id' => $dealOrderEntity->gateway_transaction_id,
            );

            // Update deal
            $idQoute = $dbWriter->quote($dealOrderEntity->id);
            $where[] = "id = $idQoute";

            // Execute query
            $numRows = $dbWriter->update($this->tableName, $data, $where);
            if($numRows > 0) {
                return true;
            }else {
                return false;
            }
        } catch(Exception $ex) {
            throw $ex;
        }
    }

    /**
     * Delete a deal by Id
     *
     * @access public
     *
     * @param int $id: Id of deal
     *
     * @return bool whether delete action is success or not
     */
    public function del($id) {
        try {
            // Get db object
            $dbWriter = $this->getDbWriter();

            // Update deal
            $idQuote = $dbWriter->quote($id);

            // Execute query
            $rowCount = $dbWriter->delete($this->tableName, "id = $idQuote");

            // Return boolean result
            if($rowCount > 0) {
                return true;
            }else {
                return false;
            }
        } catch(Exception $ex) {
            throw $ex;
        }
    }

    /**
     * Get DealEntity object by Id of the deal
     *
     * @access public
     *
     * @param int $id: Id of deal
     *
     * @return DealEntity object
     */
    public function getById ($id) {
        // Get db object
        $dbWriter = $this->getDbWriter();

        // Add quote
        $idQuote = $dbWriter->quote($id);

        // Query for get deal by Id
        $query = "
			SELECT {$this->tableName}.*
			FROM {$this->tableName} 
			WHERE {$this->tableName}.id = $idQuote 
                ";

        //echo $query;
        //die();
        $result = $dbWriter->fetchRow($query);
        if (is_null($result) || !is_array($result)) {

            // Id of the deal does not exist
            return null;
        }
        $dealOrderEntity = new DealOrderEntity($result);

        // Return DealEntity object
        return $dealOrderEntity;
    }

    /**
     * Get all rows of deal table with search criterias
     *
     * @access public
     *
     * @param array $arrElement: array of search criterias
     * @param string $orderBy: Field and the way ordered (ASC or DESC)
     * @param int $begin: Begin item
     * @param int $limit: Number rows to query
     *
     * @return array of dealEntity object
     */
    public function getItems($arrElement, $orderBy = null, $begin = null, $limit = null) {
        // Get db object
        $dbWriter = $this->getDbWriter();

        // Query for get all items of deal with search criterias
        $query = "
			SELECT {$this->tableName}.*				
			FROM {$this->tableName}";

        // Add where
        $whereTmp = "";
        $whereClause = $this->buildWhereClause($arrElement, $dbWriter, $this->tableName);
        $concat = !empty($whereClause) && !empty($whereTmp) ? ' AND ' : '';
        if(empty($whereClause) && !empty($whereTmp)) {
            $whereClause = ' WHERE ';
        }
        $query .= " {$whereClause}{$concat}{$whereTmp} ";

        // Add order By
        $query .= $this->buildOrderClause($orderBy);

        // Add begin and Limit
        $query .= $this->buildLimitClause($begin, $limit);

        //echo $query;
        //die();

        // Execute query
        $result = $dbWriter->fetchAll($query);

        $buffer = null;
        foreach ($result as $element) {
            $dealOrderEntity = new DealOrderEntity($element);

            // Add DealEntity into result
            $buffer[] = $dealOrderEntity;
        }
        return $buffer;
    }
	public function getOrderOfDeal($deal_id = null){
        // Get db object
        $dbWriter = $this->getDbWriter();

	 	$query = "
			SELECT {$this->tableName}.*, OA.*
			FROM {$this->tableName} ";
        $query .= "RIGHT JOIN order_address as OA ON (OA.deal_order_id = {$this->tableName}.id) ";
        if(!empty($deal_id)){
        	$query .= "WHERE {$this->tableName}.deal_id = {$deal_id}";
        }

        // Execute query
        $result = $dbWriter->fetchAll($query);

        return $result;
    }
    public function getOrdersOfUser($user_id){
    	 // Get db object
        $dbWriter = $this->getDbWriter();

        // Query for get all items of deal with search criterias
        $query = "
			SELECT *, D.id as deal_id, DO.id as deal_order_id			
			FROM {$this->tableName} as DO 
			JOIN deals as D ON D.id = DO.deal_id
			JOIN groupon as G ON G.id = D.groupon_id
			WHERE DO.user_id = {$user_id}
			order by order_date DESC
			";
        
        // Execute query
        $result = $dbWriter->fetchAll($query);
        return $result;
    }
    public function getOrder($order_id){
    	// Get db object
        $dbWriter = $this->getDbWriter();

        // Query for get all items of deal with search criterias
        $query = "
			SELECT *, D.id as deal_id, DO.id as deal_order_id			
			FROM {$this->tableName} as DO 
			JOIN deals as D ON D.id = DO.deal_id
			JOIN groupon as G ON G.id = D.groupon_id
			JOIN user as U ON U.id = DO.user_id
			WHERE DO.id = {$order_id}
			";
        
        // Execute query
        $result = $dbWriter->fetchAll($query);
        return $result;
    }
    /**
     * Get total rows of deal
     *
     * @access public
     *
     * @param array $arrElement: array of search criterias
     *
     * @return int total: total rows of deal
     */
    public function getTotals($arrElement) {
        // Get db object
        $dbWriter = $this->getDbWriter();

        // Query for get total records in deal table
        $query = "
			SELECT COUNT(*) as total
			FROM {$this->tableName} ";
        // Add where
        $query .= $this->buildWhereClause($arrElement, $dbWriter);

        // Execute query
        $result = $dbWriter->fetchRow($query);

        if( !is_null($result) ) {
            return (int) $result['total'];
        }

        // Return no record
        return 0;
    }
	public function getItemsAll($arrElement, $orderBy = null, $begin = null, $limit = null, $where = null, $having = null) {
        // Get db object
        $dbWriter = $this->getDbWriter();

        // Query for get all items of user deposit with search criterias
        $query = "
			SELECT OD.*, OA.*, D.status as deal_status
			FROM {$this->tableName} as OD
			JOIN order_address as OA ON OA.deal_order_id = OD.id
			JOIN deals as D ON D.id=OD.deal_id
			";

        // Add where
        $whereTmp = "";
        $whereClause = $this->buildWhereClause($arrElement, $dbWriter, 'OD');
        $concat = !empty($whereClause) && !empty($whereTmp) ? ' AND ' : '';
        if (empty($whereClause) && !empty($whereTmp)) {
            $whereClause = ' WHERE ';
        }
        $query .= " {$whereClause}{$concat}{$whereTmp} ";
        if (!empty($where)) {
            $query .= $where;
        }

        if (!empty($having)) {
            $query .= " HAVING " . $having;
        }

        // Add order By
        $query .= $this->buildOrderClause($orderBy);
        // Add begin and Limit
        $query .= $this->buildLimitClause($begin, $limit);
        // Execute query
        $result = $dbWriter->fetchAll($query);

        return $result;
    }
	public function getAllItems($arrElement, $orderBy = null, $begin = null, $limit = null, $where = null, $having = null) {
        // Get db object
        $dbWriter = $this->getDbWriter();

        // Query for get all items of user deposit with search criterias
        $query = "
			SELECT OD.*, U.*
			FROM {$this->tableName} as OD
			JOIN user as U ON U.id = OD.user_id
			";

        // Add where
        $whereTmp = "";
        $whereClause = $this->buildWhereClause($arrElement, $dbWriter, 'OD');
        $concat = !empty($whereClause) && !empty($whereTmp) ? ' AND ' : '';
        if (empty($whereClause) && !empty($whereTmp)) {
            $whereClause = ' WHERE ';
        }
        $query .= " {$whereClause}{$concat}{$whereTmp} ";
        if (!empty($where)) {
            $query .= $where;
        }

        if (!empty($having)) {
            $query .= " HAVING " . $having;
        }

        // Add order By
        $query .= $this->buildOrderClause($orderBy);
        // Add begin and Limit
        $query .= $this->buildLimitClause($begin, $limit);
        //echo $query;
        // Execute query
        $result = $dbWriter->fetchAll($query);

        return $result;
    }
   
    function DealOrderStatistics($dateTo, $dateEnd){
    	// Get db object
        $dbWriter = $this->getDbWriter();
        // Query for get the order statistics
        $query= "SELECT DO.deal_id, SUM(DO.quantity) AS `total`, D.deal_name, G.groupon_name
				FROM deals AS D
				JOIN groupon AS G ON G.id =  D.groupon_id
				JOIN `deal_order` AS DO ON DO.deal_id =  D.id
				WHERE  DATE_FORMAT(DO.order_date, '%Y-%m-%d %H:%i:%s') <= '".date('Y-m-d', strtotime($dateEnd)) ."'
				AND DATE_FORMAT(DO.order_date, '%Y-%m-%d %H:%i:%s') >= '".date('Y-m-d',  strtotime($dateTo)) ."'
				AND DO.order_status = 'completed' 
				GROUP BY DO.deal_id
				ORDER BY total DESC LIMIT 0,5"; 
         // Execute query
        //echo $query;
        $result = $dbWriter->fetchAll($query);
        return $result;
    }
    function CategoryOrderStatistics($dateTo, $dateEnd){
    	$dbWriter = $this->getDbWriter();
    	$query= "SELECT C.name, SUM(DO.quantity) AS `total` 
				FROM deals AS D
				JOIN groupon AS G ON G.id =  D.groupon_id
				JOIN `deal_order` AS DO ON DO.deal_id =  D.id
				JOIN groupon_category AS C ON C.id = G.category_id 
				WHERE  DATE_FORMAT(DO.order_date, '%Y-%m-%d %H:%i:%s') <= '".date('Y-m-d', strtotime($dateEnd)) ."'
				AND DATE_FORMAT(DO.order_date, '%Y-%m-%d %H:%i:%s') >= '".date('Y-m-d',  strtotime($dateTo)) ."'
				AND DO.order_status = 'completed' 
				GROUP BY DO.deal_id
				ORDER BY total DESC LIMIT 0,5";
    	//echo $query;
    	$result =$dbWriter->fetchAll($query);
    	return $result;
    }
    function SalesStatistics($dateTo, $dateEnd){
    	$dbWriter = $this->getDbWriter();
    	$query= "SELECT SUM(DO.total_price) AS `total`, SUM(DO.service_fee) AS fee   			 
				FROM deal_order DO
				WHERE  DATE_FORMAT(DO.order_date, '%Y-%m-%d %H:%i:%s') <= '".date('Y-m-d', strtotime($dateEnd)) ."'
				AND DATE_FORMAT(DO.order_date, '%Y-%m-%d %H:%i:%s') >= '".date('Y-m-d',  strtotime($dateTo)) ."'
				AND DO.order_status = 'completed' 
				";
    	$result =$dbWriter->fetchAll($query);
    	return $result;
    }
    public function getShippingOfStaff($param = array()){
    	// Get db object
        $dbWriter = $this->getDbWriter();
        // Query for get the order statistics
        $query= "SELECT *
				FROM `order_shipping` As OS
				JOIN `deal_order` AS DO ON DO.id = OS.deal_order_id
				JOIN `user` AS U ON U.id = OS.user_id
				JOIN `deals` as D ON D.id = DO.deal_id
				JOIN `groupon` as G ON G.id = D.groupon_id
				JOIN `order_address` as OD ON OD.deal_order_id = DO.id
				WHERE 1
				"; 
         // Execute query
         if(!empty($param['user_id'])){
         	$query .= " AND OS.user_id = {$param['user_id']}";
         }
    	 if(!empty($param['date'])){
         	$query .= " AND DATE_FORMAT(OS.shipping_date, '%Y-%m-%d')='". date('Y-m-d',  strtotime($param['date'])) ."'";
         }
    	 if(!empty($param['order_id'])){
         	$query .= " AND DO.id = {$param['order_id']}";
         }else{
         	$query .= " AND OS.status != '" .OrderShippingEntityBase::COMPLETE. "'";
         }	
        $result = $dbWriter->fetchAll($query);
        return $result;
    }
}